package com.ruoyi.system.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.extra.qrcode.QrCodeUtil;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.enums.UserType;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileUploadUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.*;
import com.ruoyi.system.domain.vo.SSubstationDeviceQRCodeVo;
import com.ruoyi.system.domain.vo.SSubstationDeviceVo;
import com.ruoyi.system.mapper.SSubstationDeviceMapper;
import com.ruoyi.system.service.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 变电站设备信息Service业务层处理
 * 
 * @author ruoyi
 * @date 2023-07-05
 */
@Service
public class SSubstationDeviceServiceImpl extends ServiceImpl<SSubstationDeviceMapper, SSubstationDevice> implements ISSubstationDeviceService
{

    @Autowired
    private SSubstationDeviceMapper substationDeviceMapper;

    @Autowired
    private ISAreaService areaService;

    @Autowired
    private ISSubstationService substationService;

    @Autowired
    private ISDeviceVoltageLevelService deviceVoltageLevelService;

    @Autowired
    private ISProtectDeviceService protectDeviceService;

    @Autowired
    private ISDeviceProtectDeviceService deviceProtectDeviceService;

    @Autowired
    private ISProtectDeviceVersionService protectDeviceVersionService;

    @Autowired
    private ISParameterDetailsService parameterDetailsService;

    @Override
    public List<SSubstationDevice> getList(SSubstationDevice sSubstationDevice) {
        // 获取设备基础信息列表
        List<SSubstationDevice> list = getDeviceList(sSubstationDevice);

        // 添加保护型号
        for (SSubstationDevice substationDevice : list) {
            String protectDeviceModel = protectDeviceService.getProtectDeviceModelByDeviceId(substationDevice.getId());
            substationDevice.setProtectDeviceModel(protectDeviceModel);
        }
        return list;
    }

    @Override
    public List<SProtectDevice> getProtectDeviceList(String id) {
        // 获取集合
        List<SProtectDevice> protectDeviceList = protectDeviceService.getProtectDeviceList(id);
        return protectDeviceList;
    }

    @Override
    public String createQRCode(String id) {

        if(StringUtils.isBlank(id)) return "";

        SSubstationDevice substationDevice = this.getById(id);
        if(StringUtils.isNull(substationDevice))  return "";
        SSubstationDeviceQRCodeVo vo = new SSubstationDeviceQRCodeVo();
        vo.setDeviceId(id);
        // 获取保护装置id集合
        List<String> list = deviceProtectDeviceService.list(new LambdaQueryWrapper<SDeviceProtectDevice>()
                        .eq(SDeviceProtectDevice::getDeviceId, id)).stream()
                .map(SDeviceProtectDevice::getProtectDeviceId).collect(Collectors.toList());
        vo.setProtectDeviceIds(list);

        // 获取变电站名称
//        if(StringUtils.isNotBlank(substationDevice.getSubstationId())) {
//            SSubstation substation = substationService.getById(substationDevice.getSubstationId());
//            substationDevice.setSubstationName(substation.getSubstationName());
//            // 获取区域名称
//            SArea area = areaService.getById(substation.getAreaId());
//            if(StringUtils.isNotNull(area)) {
//                substationDevice.setAreaName(area.getAreaName());
//            }
//        }

        // 获取电压等级值
//        if(StringUtils.isNotBlank(substationDevice.getVoltageLevel())) {
//            SDeviceVoltageLevel deviceVoltageLevel = deviceVoltageLevelService.getById(substationDevice.getVoltageLevel());
//            if(StringUtils.isNotNull(deviceVoltageLevel)) {
//                substationDevice.setVoltageLevelValue(deviceVoltageLevel.getDeviceVoltageLevel());
//            }
//        }
//        // 添加保护型号
//        if(StringUtils.isNotBlank(substationDevice.getId())) {
//            String protectDeviceModel = protectDeviceService.getProtectDeviceModelByDeviceId(substationDevice.getId());
//            substationDevice.setProtectDeviceModel(protectDeviceModel);
//        }
        BeanUtil.copyProperties(substationDevice, vo);

        // 拼接文件路径
        String filePath = RuoYiConfig.getUploadPath();
        filePath = filePath + "/QRCode/" + id + ".jpg";

        QrCodeUtil.generate(JSONUtil.toJsonStr(vo), 300, 300, FileUtil.file(filePath));

        String url = "/profile/upload/QRCode/" + id + ".jpg";
        substationDevice.setQrCodeUrl(url);
        // 更新二维码图片
        updateById(substationDevice);

        return url;
    }
    @Override
    @Transactional
    public String importData(MultipartFile file) throws Exception {

        ExcelUtil<SSubstationDevice> util = new ExcelUtil<SSubstationDevice>(SSubstationDevice.class);
        Sheet sheet = util.getSheet("", file.getInputStream());
        int lastRowNum = sheet.getLastRowNum();
        if(lastRowNum <= 0) return "数据为空！";

        // 创建设备保护装置参数列表导入版本
        SProtectDeviceVersion sProtectDeviceVersion = new SProtectDeviceVersion();
        // 获取文件日期和字号

        Row row1 = sheet.getRow(1);
        getFileDateAndFileNo(sProtectDeviceVersion, row1);

        // 判断文件是否导入过
        SProtectDeviceVersion one = protectDeviceVersionService.getOne(new LambdaQueryWrapper<SProtectDeviceVersion>()
                .eq(SProtectDeviceVersion::getFileNo, sProtectDeviceVersion.getFileNo())
                .eq(SProtectDeviceVersion::getFileDate, sProtectDeviceVersion.getFileDate()));
        if(StringUtils.isNotNull(one)) {
            return "重复导入！";
        }


        List<SParameterDetails> list = new ArrayList<>();

        // 创建变电站设备对象，用来保存表格中的前4行的值
        SSubstationDevice substationDevice = createSubstationDevice(sheet);
        // 保存装置版本号
        sProtectDeviceVersion.setVersionNo(substationDevice.getDeviceVersion());

        for (int i = 6; i < lastRowNum; i++) {
            Row parameterRow = sheet.getRow(i);
            // 参数定值表，保存参数定值表相关信息
            SParameterDetails parameterDetailsOne = new SParameterDetails();
            SParameterDetails parameterDetailsTow = new SParameterDetails();
            // 封装参数定值列表
            createParameterDetails(parameterRow, parameterDetailsOne, parameterDetailsTow);

            // 保存到列表
            if(StringUtils.isNotBlank(parameterDetailsOne.getSerialNo())){
                list.add(parameterDetailsOne);
            }
            if(StringUtils.isNotBlank(parameterDetailsTow.getSerialNo())){
                list.add(parameterDetailsTow);
            }
        }
        if(StringUtils.isNotBlank(substationDevice.getDeviceName())) {

            // 变电站信息
            SSubstation sSubstation = substationService.getOrCreateByName(substationDevice.getSubstationName());

            // 保护装置信息
            SProtectDevice protectDevice = protectDeviceService.getOrCreateByModel(substationDevice.getProtectDeviceModel());

            // 设备信息
            SSubstationDevice device = this.getOne(new LambdaQueryWrapper<SSubstationDevice>()
                    .eq(SSubstationDevice::getDeviceName, substationDevice.getDeviceName())
                    .eq(SSubstationDevice::getSubstationId, sSubstation.getId()));
            // 如果不存在该设备信息则创建
            if (StringUtils.isNull(device)) {
                substationDevice.setSubstationId(sSubstation.getId());
                substationDevice.setCreateBy(SecurityUtils.getUserId().toString());
                substationDevice.setCreateTime(new Date());

                // 设置设备电压等级
                if(StringUtils.isNotBlank(substationDevice.getDeviceName())) {
                    String deviceName = substationDevice.getDeviceName();
                    String kV = deviceName.split("kV")[0];
                    if(StringUtils.isNotBlank(kV)){
                        kV = kV + "kV";
                        SDeviceVoltageLevel deviceVoltageLevel = deviceVoltageLevelService.getOne(new LambdaQueryWrapper<SDeviceVoltageLevel>()
                                .eq(SDeviceVoltageLevel::getDeviceVoltageLevel, kV));
                        // 不存在则新建
                        if(StringUtils.isNull(deviceVoltageLevel)){
                            deviceVoltageLevel = new SDeviceVoltageLevel();
                            deviceVoltageLevel.setDeviceVoltageLevel(kV);
                            deviceVoltageLevelService.save(deviceVoltageLevel);
                        }
                        substationDevice.setVoltageLevel(deviceVoltageLevel.getId());
                    }
                }

                this.save(substationDevice);
                // 新建则创建二维码
                this.createQRCode(substationDevice.getId());
            }else{
                substationDevice = device;
            }

            // 创建设备号保护设备之间的联系
            SDeviceProtectDevice deviceProtectDevice = deviceProtectDeviceService.getOne(new LambdaQueryWrapper<SDeviceProtectDevice>()
                    .eq(SDeviceProtectDevice::getDeviceId, substationDevice.getId())
                    .eq(SDeviceProtectDevice::getProtectDeviceId, protectDevice.getId()));
            if(StringUtils.isNull(deviceProtectDevice)){
                deviceProtectDevice = new SDeviceProtectDevice();
                deviceProtectDevice.setDeviceId(substationDevice.getId());
                deviceProtectDevice.setProtectDeviceId(protectDevice.getId());
                deviceProtectDeviceService.save(deviceProtectDevice);
            }

            // 导入设备保护装置参数列表
            sProtectDeviceVersion.setDeviceId(substationDevice.getId());
            sProtectDeviceVersion.setProtectDeviceId(protectDevice.getId());

            protectDeviceVersionService.save(sProtectDeviceVersion);

            // 批量添加参数列表值
            list.stream().forEach(v -> v.setDeviceProtectVersionId(sProtectDeviceVersion.getId()));
            parameterDetailsService.saveBatch(list);
            // 获取最近的三个版本
            List<SProtectDeviceVersion> deviceVersions = protectDeviceVersionService.list(new LambdaQueryWrapper<SProtectDeviceVersion>()
                    .eq(SProtectDeviceVersion::getDeviceId, sProtectDeviceVersion.getDeviceId())
                    .eq(SProtectDeviceVersion::getProtectDeviceId, sProtectDeviceVersion.getProtectDeviceId())
                    .orderByDesc(SProtectDeviceVersion::getFileDate)
                    .last("limit 0, 3"));
            // 获取三个版本的id
            List<String> deviceVersionIds = deviceVersions.stream().map(SProtectDeviceVersion::getId).collect(Collectors.toList());

            // 检查参数定值是否发生变化
            // 获取山歌版本的参数定值列表
            List<List<SParameterDetails>> checkList = new ArrayList<>();
            for (String deviceVersionId : deviceVersionIds) {
                List<SParameterDetails> parameterDetailsList = parameterDetailsService.list(new LambdaQueryWrapper<SParameterDetails>().eq(SParameterDetails::getDeviceProtectVersionId, deviceVersionId));
                checkList.add(parameterDetailsList);
            }
            parameterDetailsService.checkChangeAndMark(checkList);

            return "导入成功！";
        }


        return "导入成功";
    }

//    @Override
//    @Transactional
//    public String importData1(MultipartFile file) throws Exception {
//        // 创建临时文件
////        String temp = FileUploadUtils.upload(RuoYiConfig.getTemp(), file);
////        String s = temp.replace("/profile", RuoYiConfig.getTemp());
//        Document doc = new Document();
////        doc.loadFromFile(s);
//
//        doc.loadFromFile("C:\\Users\\86152\\Desktop\\test.docx");
//
//        // 创建设备保护装置参数列表导入版本
//        SProtectDeviceVersion sProtectDeviceVersion = new SProtectDeviceVersion();
//
//        // 获取文件日期和字号
//        String text = doc.getText();
//        getFileDateAndFileNo(sProtectDeviceVersion, text);
//
//        // 判断文件是否导入过
//        SProtectDeviceVersion one = protectDeviceVersionService.getOne(new LambdaQueryWrapper<SProtectDeviceVersion>()
//                .eq(SProtectDeviceVersion::getFileNo, sProtectDeviceVersion.getFileNo())
//                .eq(SProtectDeviceVersion::getFileDate, sProtectDeviceVersion.getFileDate()));
//        if(StringUtils.isNotNull(one)) {
//            return "重复导入！";
//        }
//
//        SectionCollection sections = doc.getSections();
//        Section section = sections.get(0);
//        Body body = section.getBody();
//        TableCollection tables = body.getTables();
//
//        if(tables != null) {
//            Table table = tables.get(0);
//
//            List<SParameterDetails> list = new ArrayList<>();
//            int begin = table.getRows().getCount() + 1;
//
//            // 创建变电站设备对象，用来保存表格中的前4行的值
//            SSubstationDevice substationDevice = new SSubstationDevice();
//            //遍历表格中的段落并提取文本
//            for (int i = 0; i < table.getRows().getCount() - 1; i++) {
//
//                // 获取行
//                TableRow row = table.getRows().get(i);
//
//                // 参数定值表，保存参数定值表相关信息
//                SParameterDetails parameterDetailsOne = new SParameterDetails();
//                SParameterDetails parameterDetailsTow = new SParameterDetails();
//
//                if(i > begin) {
//                    // 封装参数定值列表
//                    createParameterDetails(row, parameterDetailsOne, parameterDetailsTow);
//
//                    // 保存到列表
//                    if(StringUtils.isNotBlank(parameterDetailsOne.getSerialNo())){
//                        list.add(parameterDetailsOne);
//                    }
//                    if(StringUtils.isNotBlank(parameterDetailsTow.getSerialNo())){
//                        list.add(parameterDetailsTow);
//                    }
//                }
//                for (int j = 0; j < row.getCells().getCount() - 1; j++) {
//                    // 封装变电站设备对象
//                    createSubstationDevice(substationDevice, row, j);
//
//                    TableCell cell = row.getCells().get(j);
//                    Paragraph paragraph = cell.getParagraphs().get(0);
//                    if("序号".equals(paragraph.getText())){
//                        begin = i;
//                        j = 999;
//                    }
//                }
//            }
//            if(StringUtils.isNotBlank(substationDevice.getDeviceName())) {
//
//                // 变电站信息
//                SSubstation sSubstation = substationService.getOrCreateByName(substationDevice.getSubstationName());
//
//                // 保护装置信息
//                SProtectDevice protectDevice = protectDeviceService.getOrCreateByModel(substationDevice.getProtectDeviceModel());
//
//                // 设备信息
//                SSubstationDevice device = this.getOne(new LambdaQueryWrapper<SSubstationDevice>()
//                        .eq(SSubstationDevice::getDeviceName, substationDevice.getDeviceName())
//                        .eq(SSubstationDevice::getSubstationId, sSubstation.getId()));
//                // 如果不存在该设备信息则创建
//                if (StringUtils.isNull(device)) {
//                    substationDevice.setSubstationId(sSubstation.getId());
//                    substationDevice.setCreateBy(SecurityUtils.getUserId().toString());
//                    substationDevice.setCreateTime(new Date());
//                    this.save(substationDevice);
//                    // 新建则创建二维码
//                    this.createQRCode(substationDevice.getId());
//                }else{
//                    substationDevice = device;
//                }
//
//                // 创建设备号保护设备之间的联系
//                SDeviceProtectDevice deviceProtectDevice = deviceProtectDeviceService.getOne(new LambdaQueryWrapper<SDeviceProtectDevice>()
//                        .eq(SDeviceProtectDevice::getDeviceId, substationDevice.getId())
//                        .eq(SDeviceProtectDevice::getProtectDeviceId, protectDevice.getId()));
//                if(StringUtils.isNull(deviceProtectDevice)){
//                    deviceProtectDevice = new SDeviceProtectDevice();
//                    deviceProtectDevice.setDeviceId(substationDevice.getId());
//                    deviceProtectDevice.setProtectDeviceId(protectDevice.getId());
//                    deviceProtectDeviceService.save(deviceProtectDevice);
//                }
//
//                // 导入设备保护装置参数列表
//                sProtectDeviceVersion.setDeviceId(substationDevice.getId());
//                sProtectDeviceVersion.setProtectDeviceId(protectDevice.getId());
//                protectDeviceVersionService.save(sProtectDeviceVersion);
//
//                // 批量添加参数列表值
//                list.stream().forEach(v -> v.setDeviceProtectVersionId(sProtectDeviceVersion.getId()));
//                parameterDetailsService.saveBatch(list);
//                // 获取最近的三个版本
//                List<SProtectDeviceVersion> deviceVersions = protectDeviceVersionService.list(new LambdaQueryWrapper<SProtectDeviceVersion>()
//                        .eq(SProtectDeviceVersion::getDeviceId, sProtectDeviceVersion.getDeviceId())
//                        .eq(SProtectDeviceVersion::getProtectDeviceId, sProtectDeviceVersion.getProtectDeviceId())
//                        .orderByDesc(SProtectDeviceVersion::getFileDate)
//                        .last("limit 0, 3"));
//                // 获取三个版本的id
//                List<String> deviceVersionIds = deviceVersions.stream().map(SProtectDeviceVersion::getId).collect(Collectors.toList());
//
//                // 检查参数定值是否发生变化
//                // 获取山歌版本的参数定值列表
//                List<List<SParameterDetails>> checkList = new ArrayList<>();
//                for (String deviceVersionId : deviceVersionIds) {
//                    List<SParameterDetails> parameterDetailsList = parameterDetailsService.list(new LambdaQueryWrapper<SParameterDetails>().eq(SParameterDetails::getDeviceProtectVersionId, deviceVersionId));
//                    checkList.add(parameterDetailsList);
//                }
//                parameterDetailsService.checkChangeAndMark(checkList);
//
//                // 删除临时文件
////                            FileUploadUtils.remove(s);
//                return "导入成功！";
//            }
//        }
//
//
//
//
//        // 删除临时文件
////        FileUploadUtils.remove(s);
//        return "导入成功";
//    }

    @Override
    public boolean checkSubstationDevice(SSubstationDevice substationDevice) {
        if(StringUtils.isBlank(substationDevice.getDeviceName())){
            throw new ServiceException("设备名称不能为空！");
        }
        long count = this.count(new LambdaQueryWrapper<SSubstationDevice>()
                .eq(SSubstationDevice::getDeviceName, substationDevice.getDeviceName()));
        if(count > 0){
            throw new ServiceException("设备名称：" + substationDevice.getDeviceName() + " 已存在！");
        }
        return true;
    }

    @Override
    public List<Map<String, Object>> detailList(SSubstationDevice sSubstationDevice) {
        // 获取设备基础信息列表
        List<SSubstationDevice> list = getDeviceList(sSubstationDevice);
        List<Map<String, Object>> mapList = new ArrayList<>();

        for (SSubstationDevice substationDevice : list) {
            HashMap<String, Object> map = new HashMap<>();
            map.put("deviceName", substationDevice.getDeviceName());
            map.put("deviceId", substationDevice.getId());
            // 获取保护装置型号id集合
            List<String> protectDeviceIds = deviceProtectDeviceService.list(new LambdaQueryWrapper<SDeviceProtectDevice>()
                    .eq(SDeviceProtectDevice::getDeviceId, substationDevice.getId()))
                    .stream().map(SDeviceProtectDevice::getProtectDeviceId).collect(Collectors.toList());
            List<Map<String, Object>> protectList = new ArrayList<>();
            for (String protectDeviceId : protectDeviceIds) {
                HashMap<String, Object> protectMap = new HashMap<>();
                // 保护装置id
                protectMap.put("protectDeviceId", protectDeviceId);

                // 获取最近的三个版本信息
                List<SProtectDeviceVersion> deviceVersions = protectDeviceVersionService.list(new LambdaQueryWrapper<SProtectDeviceVersion>()
                        .eq(SProtectDeviceVersion::getDeviceId, substationDevice.getId())
                        .eq(SProtectDeviceVersion::getProtectDeviceId, protectDeviceId)
                        .orderByDesc(SProtectDeviceVersion::getFileDate)
                        .last("limit 0, 3"));
                protectMap.put("version", deviceVersions);

                protectList.add(protectMap);
            }
            map.put("protectList", protectList);
            mapList.add(map);
        }

        return mapList;
    }

    @Override
    public Map<String, Object> scanQrCode(SSubstationDeviceVo vo) {
        String deviceId = vo.getDeviceId();
        // 获取保护装置id集合
        List<String> protectDeviceIds = deviceProtectDeviceService.list(new LambdaQueryWrapper<SDeviceProtectDevice>()
                            .eq(SDeviceProtectDevice::getDeviceId, deviceId)).stream()
                    .map(SDeviceProtectDevice::getProtectDeviceId).collect(Collectors.toList());

        // 获取设备信息
        SSubstationDevice substationDevice = this.getById(deviceId);
        if(StringUtils.isNull(substationDevice)) return null;

        HashMap<String, Object> map = new HashMap<>();
        // 设备id
        map.put("deviceId", deviceId);
        // 设备名称
        map.put("deviceName", substationDevice.getDeviceName());
        // 变电站id
        map.put("substationId", substationDevice.getSubstationId());
        SSubstation substation = substationService.getById(substationDevice.getSubstationId());
        if(StringUtils.isNotNull(substation)) {
            // 变电站名称
            map.put("substationName", substation.getSubstationName());
        }
        // 地区id
        map.put("areaId", substation.getAreaId());
        if(StringUtils.isNotBlank(substation.getAreaId())){
            SArea area = areaService.getById(substation.getAreaId());
            if(StringUtils.isNotNull(area)) {
                map.put("areaName", area.getAreaName());
            }
        }
        // 电压等级id
        map.put("voltageLevelId", substationDevice.getVoltageLevel());
        // 电压等级值
        if(StringUtils.isNotBlank(substationDevice.getVoltageLevel())){
            SDeviceVoltageLevel deviceVoltageLevel = deviceVoltageLevelService.getById(substationDevice.getVoltageLevel());
            if(StringUtils.isNotNull(deviceVoltageLevel)) {
                map.put("voltageLevelValue", deviceVoltageLevel.getDeviceVoltageLevel());
            }
        }

        List<Map<String, Object>> protectList = new ArrayList<>();
        for (String protectDeviceId : protectDeviceIds) {
            HashMap<String, Object> protectMap = new HashMap<>();
            // 保护装置id
            protectMap.put("protectDeviceId", protectDeviceId);

            // 获取最近的三个版本信息
            List<SProtectDeviceVersion> deviceVersions = protectDeviceVersionService.list(new LambdaQueryWrapper<SProtectDeviceVersion>()
                    .eq(SProtectDeviceVersion::getDeviceId, deviceId)
                    .eq(SProtectDeviceVersion::getProtectDeviceId, protectDeviceId)
                    .orderByDesc(SProtectDeviceVersion::getFileDate)
                    .last("limit 0, 3"));
            protectMap.put("version", deviceVersions);

            protectList.add(protectMap);
        }

        // 电压等级id
        map.put("protectList", protectList);
        return map;
    }

    @Transactional
    @Override
    public boolean removeSubstationDeviceByIds(List<String> ids) {
        // 删除图片
        for (String id : ids) {
            String path = RuoYiConfig.getProfile() + "/upload/QRCode/" + id + ".jpg";
            FileUploadUtils.remove(path);
        }
        // 删除与保护装置的关联
        deviceProtectDeviceService.remove(new LambdaQueryWrapper<SDeviceProtectDevice>()
                .in(SDeviceProtectDevice::getDeviceId, ids));
        // 删除关联版本
        protectDeviceVersionService.remove(new LambdaQueryWrapper<SProtectDeviceVersion>()
                .in(SProtectDeviceVersion::getDeviceId, ids));

        return this.removeByIds(ids);
    }

    @Override
    public Map<String, String> deviceNameList(SSubstationDevice sSubstationDevice) {
        // 获取所属班组长ID
        String leaderId = SecurityUtils.getLeaderId();
        List<String> substationIds =  new ArrayList<>();
        if(StringUtils.isNotBlank(leaderId)) {
            // 获取所属班组长下的所有变电站id集合
            List<String> areaIds = areaService.list(new LambdaQueryWrapper<SArea>().eq(SArea::getUserId, leaderId))
                    .stream().map(SArea::getId).collect(Collectors.toList());
            // 不存在区域则直接返回
            if(StringUtils.isNotNull(areaIds) && areaIds.size() > 0) return null;

            // 获取变电站ID集合
            List<SSubstation> substationList = substationService.list(new LambdaQueryWrapper<SSubstation>()
                    .in( SSubstation::getAreaId, areaIds));
            if(StringUtils.isNotNull(substationList) && substationList.size() > 0){
                substationIds = substationList.stream().map(SSubstation::getId).collect(Collectors.toList());
            }
        }
        // 获取变电站设备列表
        List<SSubstationDevice> list = this.list(new LambdaQueryWrapper<SSubstationDevice>()
                .like(StringUtils.isNotBlank(sSubstationDevice.getDeviceName()),SSubstationDevice::getDeviceName, sSubstationDevice.getDeviceName())
                .in(substationIds.size() > 0, SSubstationDevice::getSubstationId, substationIds)
                .orderByAsc(SSubstationDevice::getDeviceName));
        // 封装数据
        Map<String, String> map = list.stream().collect(Collectors.toMap(SSubstationDevice::getId, SSubstationDevice::getDeviceName));
        return map;
    }


    private List<SSubstationDevice> getDeviceList(SSubstationDevice sSubstationDevice) {
        // 获取当前登录人员信息
        SysUser user = SecurityUtils.getLoginUser().getUser();
        QueryWrapper<SSubstationDevice> queryWrapper = sSubstationDevice.queryWrapper();
        // 班组长和组员只能查看绑定的变电站相关信息
        String leaderId = "";
        // 班组长
        if(UserType.LEADER.getCode().equals(user.getUserType())){
            leaderId = user.getUserId().toString();
        }
        // 组员
        if(UserType.COMMON.getCode().equals(user.getUserType())){
            leaderId = user.getLeaderId();
        }
        // 添加区域条件
        if(StringUtils.isBlank(sSubstationDevice.getAreaId())) {
            if (StringUtils.isNotBlank(leaderId)) {
                List<String> list = areaService.list(new LambdaQueryWrapper<SArea>().eq(SArea::getUserId, leaderId))
                        .stream().map(SArea::getId).collect(Collectors.toList());
                if(list.size() > 0) {
                    queryWrapper.in("s.area_id", list);
                }else{
                    return new ArrayList<>();
                }
            }
        }else{
            queryWrapper.eq("s.area_id", sSubstationDevice.getAreaId());
        }
        List<SSubstationDevice> list = substationDeviceMapper.getList(queryWrapper);
        return list;
    }

    private void getFileDateAndFileNo(SProtectDeviceVersion sProtectDeviceVersion, Row row) {

        for (Cell cell : row) {
            String value = cell.getStringCellValue();
            // 获取字号
            if(StringUtils.isNotBlank(sProtectDeviceVersion.getFileDate())){
                if(StringUtils.isNotBlank(value)){
                    sProtectDeviceVersion.setFileNo(value);
                    break;
                }
            }
            // 获取日期
            if(value.contains("年") && value.contains("月") && value.contains("日")){
                sProtectDeviceVersion.setFileDate(value);
            }
        }
    }

//    private void createSubstationDevice(SSubstationDevice substationDevice, TableRow row, int j) {
//        TableCell cell = row.getCells().get(j);
//        Paragraph paragraph = cell.getParagraphs().get(0);
//        if("厂站名称".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String substationName = cell2.getParagraphs().get(0).getText();
//            substationDevice.setSubstationName(substationName);
//        }else if("设备名称".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String deviceName = cell2.getParagraphs().get(0).getText();
//            substationDevice.setDeviceName(deviceName);
//        }else if("开关编号".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String switchNo = cell2.getParagraphs().get(0).getText();
//            substationDevice.setSwitchNo(switchNo);
//        }else if("保护型号".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String protectDeviceModel = cell2.getParagraphs().get(0).getText();
//            substationDevice.setProtectDeviceModel(protectDeviceModel);
//        }else if("调度名称".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String scheduleName = cell2.getParagraphs().get(0).getText();
//            substationDevice.setScheduleName(scheduleName);
//        }else if("装置版本号".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String deviceVersion = cell2.getParagraphs().get(0).getText();
//            substationDevice.setDeviceVersion(deviceVersion);
//        }else if("校验码".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String checkCode = cell2.getParagraphs().get(0).getText();
//            substationDevice.setCheckCode(checkCode);
//        }else if("CT变比".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String ctTransformationRatio = cell2.getParagraphs().get(0).getText();
//            substationDevice.setCtTransformationRatio(ctTransformationRatio);
//        }else if("PT变比".equals(paragraph.getText())){
//            TableCell cell2 = row.getCells().get(j + 1);
//            String ptTransformationRatio = cell2.getParagraphs().get(0).getText();
//            substationDevice.setPtTransformationRatio(ptTransformationRatio);
//        }
//    }

    private String getNextValue(int i, Row row){
        for (; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            String value = cell.getStringCellValue();
            if(StringUtils.isNotBlank(value)){
                return value;
            }
        }
        return "";
    }

    @Override
    public SSubstationDevice createSubstationDevice(Sheet sheet) {
        SSubstationDevice substationDevice = new SSubstationDevice();
        for (int j = 2; j <= 5; j++) {
            Row row = sheet.getRow(j);
            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                String value = cell.getStringCellValue();
                if(StringUtils.isBlank(value)) continue;
                value = value.trim();
                if("厂站名称".equals(value)){
                    String substationName = getNextValue(i + 1, row);
                    substationDevice.setSubstationName(substationName);
                }else if("设备名称".equals(value)){
                    String deviceName = getNextValue(i + 1, row);
                    substationDevice.setDeviceName(deviceName);
                }else if("开关编号".equals(value)){
                    String switchNo = getNextValue(i + 1, row);
                    substationDevice.setSwitchNo(switchNo);
                }else if("装置型号".equals(value)){
                    String protectDeviceModel = getNextValue(i + 1, row);
                    substationDevice.setProtectDeviceModel(protectDeviceModel);
                }else if("调度命名".equals(value)){
                    String scheduleName = getNextValue(i + 1, row);
                    substationDevice.setScheduleName(scheduleName);
                }else if("版本号".equals(value)){
                    String deviceVersion = getNextValue(i + 1, row);
                    substationDevice.setDeviceVersion(deviceVersion);
                }else if("校验码".equals(value)){
                    String checkCode = getNextValue(i + 1, row);
                    substationDevice.setCheckCode(checkCode);
                }else if("CT变比".equals(value)){
                    String ctTransformationRatio = getNextValue(i + 1, row);
                    substationDevice.setCtTransformationRatio(ctTransformationRatio);
                }else if("PT变比".equals(value)){
                    String ptTransformationRatio = getNextValue(i + 1, row);
                    substationDevice.setPtTransformationRatio(ptTransformationRatio);
                }
            }
        }

        return substationDevice;
    }

    private String getFirstValue(int i, Row row){
        for (; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if(StringUtils.isNull(cell))
                return "";
            String value = cell.getStringCellValue();
            if(StringUtils.isNotBlank(value)){
                return value;
            }
        }
        return "";
    }
    private int getValueCount(Row row){
        int count = 0;
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            String value = cell.getStringCellValue();
            if(StringUtils.isNotBlank(value)){
                count++;
            }
        }
        return count;
    }
    private String getPositionValue(int i, Row row){
        int count = 0;
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            String value = cell.getStringCellValue();
            if(StringUtils.isNotBlank(value)){
                count++;
                if(count == i) {
                    return value;
                }
            }
        }
        return "";
    }
    private void createParameterDetails(Row row, SParameterDetails parameterDetailsOne, SParameterDetails parameterDetailsTow) {

        for (int i = 0; i < row.getLastCellNum(); i++) {
            String firstValue = getFirstValue(i, row);
            // 如果第一个字不是数字类型
            if(!StringUtils.isNumeric(firstValue)) return;


            int count = getValueCount(row);
            if(count < 3) return;
            if (count == 6) {
                parameterDetailsOne.setSerialNo(getPositionValue(1, row));
                parameterDetailsOne.setName(getPositionValue(2, row));
                parameterDetailsOne.setConstantValue(getPositionValue(3, row));
                parameterDetailsTow.setSerialNo(getPositionValue(4, row));
                parameterDetailsTow.setName(getPositionValue(5, row));
                parameterDetailsTow.setConstantValue(getPositionValue(6, row));
            }

            if (count == 5) {
                String positionValue = getPositionValue(4, row);
                if(StringUtils.isNumeric(positionValue)){
                    parameterDetailsOne.setSerialNo(getPositionValue(1, row));
                    parameterDetailsOne.setName(getPositionValue(2, row));
                    parameterDetailsOne.setConstantValue(getPositionValue(3, row));
                    parameterDetailsTow.setSerialNo(getPositionValue(4, row));
                    parameterDetailsTow.setName(getPositionValue(5, row));
                }else{
                    parameterDetailsOne.setSerialNo(getPositionValue(1, row));
                    parameterDetailsOne.setName(getPositionValue(2, row));
                    parameterDetailsTow.setSerialNo(getPositionValue(3, row));
                    parameterDetailsTow.setName(getPositionValue(4, row));
                    parameterDetailsTow.setConstantValue(getPositionValue(5, row));
                }
            }

            if (count == 4) {
                parameterDetailsOne.setSerialNo(getPositionValue(1, row));
                parameterDetailsOne.setName(getPositionValue(2, row));
                parameterDetailsTow.setSerialNo(getPositionValue(3, row));
                parameterDetailsTow.setName(getPositionValue(4, row));
            }

            if (count == 3) {
                parameterDetailsOne.setSerialNo(getPositionValue(1, row));
                parameterDetailsOne.setName(getPositionValue(2, row));
                parameterDetailsOne.setConstantValue(getPositionValue(3, row));
            }
        }
    }


//    private void createParameterDetails(TableRow row, SParameterDetails parameterDetailsOne, SParameterDetails parameterDetailsTow) {
//
//        int count = row.getCells().getCount();
//
//        if (count == 6) {
//            parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//            parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//            parameterDetailsOne.setConstantValue(row.getCells().get(2).getParagraphs().get(0).getText());
//            parameterDetailsTow.setSerialNo(row.getCells().get(3).getParagraphs().get(0).getText());
//            parameterDetailsTow.setName(row.getCells().get(4).getParagraphs().get(0).getText());
//            parameterDetailsTow.setConstantValue(row.getCells().get(5).getParagraphs().get(0).getText());
//        }
//
//        if (count == 5) {
//            if(row.getCells().get(3).getParagraphs().get(0).getText().contains(".")
//                    || row.getCells().get(3).getParagraphs().get(0).getText().length() == 1) {
//                parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                parameterDetailsOne.setConstantValue(row.getCells().get(2).getParagraphs().get(0).getText());
//                parameterDetailsTow.setSerialNo(row.getCells().get(3).getParagraphs().get(0).getText());
//                parameterDetailsTow.setName(row.getCells().get(4).getParagraphs().get(0).getText());
//            }else{
//                parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                parameterDetailsTow.setSerialNo(row.getCells().get(2).getParagraphs().get(0).getText());
//                parameterDetailsTow.setName(row.getCells().get(3).getParagraphs().get(0).getText());
//                parameterDetailsTow.setConstantValue(row.getCells().get(4).getParagraphs().get(0).getText());
//            }
//        }
//
//        if (count == 4) {
//            parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//            parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//            parameterDetailsTow.setSerialNo(row.getCells().get(2).getParagraphs().get(0).getText());
//            parameterDetailsTow.setName(row.getCells().get(3).getParagraphs().get(0).getText());
//        }
//
//        if (count == 3) {
//            parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//            parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//            parameterDetailsOne.setConstantValue(row.getCells().get(2).getParagraphs().get(0).getText());
//        }
//    }

//    public static void main(String[]args) throws IOException {
//
//        //加载Word测试文档
//        Document doc = new Document();
//        doc.loadFromFile("C:\\Users\\86152\\Desktop\\test.docx");
//
//        String text = doc.getText();
//        String s1 = text.replaceAll(" ", "").replaceAll("\t","");
//
//        String[] s = s1.split("\n");
//        for (int i = 0; i < s.length; i++) {
//            if(s[i].contains("年") && s[i].contains("月") &&s[i].contains("日")){
//                String s2 = s[i];
//                System.out.println(s2);
//
//                String[] ss = s2.split("日");
//                System.out.println(ss[0] + "日");
//                System.out.println(ss[1]);
//            }
//        }


//        SectionCollection sections = doc.getSections();
//        Section section = sections.get(0);
//        Body body = section.getBody();
//        TableCollection tables = body.getTables();
//        List<SParameterDetails> list = new ArrayList<>();
//        if(tables != null) {
//            Table table = tables.get(0);
//            int begin = table.getRows().getCount() + 1;
//            //遍历表格中的段落并提取文本
//            for (int i = 0; i < table.getRows().getCount() - 1; i++) {
//
//                TableRow row = table.getRows().get(i);
//
//                SParameterDetails parameterDetailsOne = new SParameterDetails();
//                SParameterDetails parameterDetailsTow = new SParameterDetails();
//
//
//
////                ParagraphCollection paragraphs = cell.getParagraphs();
//
//
//                int count = row.getCells().getCount();
//                if(i > begin) {
//
//                    if (count == 6) {
//                        parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                        parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                        parameterDetailsOne.setConstantValue(row.getCells().get(2).getParagraphs().get(0).getText());
//                        parameterDetailsTow.setSerialNo(row.getCells().get(3).getParagraphs().get(0).getText());
//                        parameterDetailsTow.setName(row.getCells().get(4).getParagraphs().get(0).getText());
//                        parameterDetailsTow.setConstantValue(row.getCells().get(5).getParagraphs().get(0).getText());
//                    }
//
//                    if (count == 5) {
//                        if(row.getCells().get(3).getParagraphs().get(0).getText().contains(".")
//                                || row.getCells().get(3).getParagraphs().get(0).getText().length() == 1) {
//                            parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                            parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                            parameterDetailsOne.setConstantValue(row.getCells().get(2).getParagraphs().get(0).getText());
//                            parameterDetailsTow.setSerialNo(row.getCells().get(3).getParagraphs().get(0).getText());
//                            parameterDetailsTow.setName(row.getCells().get(4).getParagraphs().get(0).getText());
//                        }else{
//                            parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                            parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                            parameterDetailsTow.setSerialNo(row.getCells().get(2).getParagraphs().get(0).getText());
//                            parameterDetailsTow.setName(row.getCells().get(3).getParagraphs().get(0).getText());
//                            parameterDetailsTow.setConstantValue(row.getCells().get(4).getParagraphs().get(0).getText());
//                        }
//                    }
//
//                    if (count == 4) {
//                        parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                        parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                        parameterDetailsTow.setSerialNo(row.getCells().get(2).getParagraphs().get(0).getText());
//                        parameterDetailsTow.setName(row.getCells().get(3).getParagraphs().get(0).getText());
//                    }
//
//                    if (count == 3) {
//                        parameterDetailsOne.setSerialNo(row.getCells().get(0).getParagraphs().get(0).getText());
//                        parameterDetailsOne.setName(row.getCells().get(1).getParagraphs().get(0).getText());
//                        parameterDetailsOne.setConstantValue(row.getCells().get(2).getParagraphs().get(0).getText());
//                    }
//
//
//                    if(StringUtils.isNotBlank(parameterDetailsOne.getSerialNo())){
//                        list.add(parameterDetailsOne);
//                    }
//                    if(StringUtils.isNotBlank(parameterDetailsTow.getSerialNo())){
//                        list.add(parameterDetailsTow);
//                    }
//                }
//                for (int j = 0; j < row.getCells().getCount(); j++) {
//                    TableCell cell = row.getCells().get(j);
//
//                    for (int k = 0; k < cell.getParagraphs().getCount(); k++) {
//                        Paragraph paragraph = cell.getParagraphs().get(k);
//                        if("序号".equals(paragraph.getText())){
//                            begin = i;
//                            k = 999;
//                            j = 999;
//                        }
//                    }
//
//                }
//            }
//        }
//
//        list.forEach(System.out::println);



}
